#第三周作業
#1) 在命令行模式下登录MySQL数据库，使用SQL语句如下要求： 

# # #1. 创建留言数据库: blogdb; =============================================================================================================================
# # #gosql.sql = '''create database blogdb'''
# # #gosql.exe()

# # #2. 在blogdb数据库中创建会员表users和博客文章表blog=============================================================================================================================
# # # gosql.sql = '''
# # # CREATE TABLE users (
# # #   `Id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID號',
# # #   `Name` varchar(32) UNIQUE NOT NULL COMMENT '姓名',
# # #   `Email` varchar(100) NOT NULL COMMENT '郵箱地址',
# # #   `cdate` datetime NOT NULL COMMENT '註冊時間',
# # #   PRIMARY KEY (`Id`)
# # # ) ENGINE=InnoDB DEFAULT CHARSET=utf8
# # # '''
# # #=============================================================================================================================
# # # gosql.sql = '''
# # # CREATE TABLE blog (
# # #   `Id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID號',
# # #   `title` varchar(100) NOT NULL COMMENT '標題',
# # #   `abstract` varchar(200) NOT NULL COMMENT '摘要',
# # #   `content` text NOT NULL COMMENT '博文內容',
# # #   `uid` int unsigned NULL COMMENT '用戶標誌',
# # #   `pcount` int NOT NULL default '0' COMMENT '點讚數',
# # #   `flag` tinyint(1) default '0' COMMENT '狀態(0:新建,1:發布,2:刪除)',
# # #   `cdate` datetime NOT NULL COMMENT '註冊時間',
# # #   PRIMARY KEY (`Id`)
# # # ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
# # # '''

# # #3. 在会员表users中添加>=5条的测试数据。 =============================================================================================================================
# # # table_name = "users"
# # # tup1 = "id","name","email","cdate"  #tuple for fields
# # # tup2 = '''
# # # ("1","Johnny","Johnny@csdn.com","2019-2-13 16:30:10" ),
# # # ("2","Monica","Monica@csdn.com","2019-2-14 16:30:10"),
# # # ("3","Joey","Joey@csdn.com","2019-2-15 16:30:10"),
# # # ("4","Reachel","Reachel@csdn.com","2019-2-16 16:30:10"),
# # # ("5","Ross","Ross@csdn.com","2019-2-17 16:30:10")
# # # '''  #tuple for values
# # # gosql.insert(table_name,tup1,tup2)

# # #4. 在blog博文信息表中添加>=10条的测试数据。=============================================================================================================================
# # # table_name = "blog"
# # # tup1 = "title","abstract","content","uid","pcount","flag","cdate"  #tuple for fields
# # # tup2 = '''
# # # ("孙立平教授把中国房地产税扯到宇宙外","Johnny's博文摘要","Johnny's博文內容","1","6548","0","2019-3-13 17:30:10"),
# # # ("邱林：两起空难原因如出一辙，波音公司到底隐瞒了什么","Johnny's博文摘要","Johnny's博文內容","1","7915","0","2019-3-13 17:35:10"),
# # # ("马跃成：像买汽车一样人人都能买得起房的时代快到了？","Joey's博文摘要","Joey's博文內容","3","26871","0","2019-3-13 16:30:10"),
# # # ("陈航：揭开史上最大美国名校录取作假案内幕(图)","Reachel's博文摘要","Reachel's博文內容","4","6511","0","2019-3-13 16:45:10"),
# # # ("这个妈妈真粗心，飞机起飞后才发现宝宝还落在候机楼(图)","Reachel's博文摘要","Reachel's博文內容","4","12687","0","2019-3-13 16:55:10"),
# # # ("潘幸知：这3代女人的悲剧，藏着大多数婚姻死掉的真相(图)","Reachel's博文摘要","Reachel's博文內容","4","8794","0","2019-3-13 17:55:10"),
# # # ("美味挡不住麻香过瘾又好吃的干锅鸡(图)","Reachel's博文摘要","Reachel's博文內容","4","2547","0","2019-3-13 17:56:10"),
# # # ("印度塔尔沙漠 夕阳驼影","Ross's博文摘要","Ross's博文內容","5","3674","0","2019-3-13 20:50:40")
# # # ("在大型项目开发上，Python 真是个“烂”语言！","Johnny's博文摘要","Johnny's博文內容","1","9456","0","2019-3-13 02:30:10"),
# # # ("人脸识别（一）——人脸识别简介","Johnny's博文摘要","Johnny's博文內容","1","5731","0","2019-3-13 03:35:10"),
# # # ("案例上手 Python 数据可视化","Joey's博文摘要","Joey's博文內容","3","9978","0","2019-3-13 04:30:10"),
# # # ("如何在面试中介绍自己的项目经验","Reachel's博文摘要","Reachel's博文內容","4","4200","0","2019-3-13 05:45:10"),
# # # ("一名6年程序员的工作感悟，写给还在迷茫中的朋友","Reachel's博文摘要","Reachel's博文內容","4","4578","0","2019-3-13 06:55:10"),
# # # ("程序员被老板要求两个月做个app，要不比京东差，网友：辞职吧","Reachel's博文摘要","Reachel's博文內容","4","4962","0","2019-3-13 07:55:10"),
# # # ("我面试过没有上万人也有十几个，简历要这么写才有hr要你","Reachel's博文摘要","Reachel's博文內容","4","21277","0","2019-3-13 08:56:10"),
# # # ("入行必看_老程序员对准程序员满满的爱","Ross's博文摘要","Ross's博文內容","5","489","0","2019-3-13 20:50:40")
# # # '''  #tuple for values
# # # gosql.insert(table_name,tup1,tup2)

# # #5. 最后将blogdb数据库中的信息导出，并以blogdb.sql文件存储待上交作业。 =============================================================================================================================
# # # cmd(run as administrator) -> mysqldump -rroot -pabc bloddb > C:\bloddb.sql




#2) 如第一题的表结构所示，按下面要求写出对应的SQL语句。 

# # #1. 在users表中查询注册时间最早的十条会员信息。 =============================================================================================================================
# # # mysql> select * from users order by cdate desc limit 10;
# # # +----+---------+------------------+---------------------+
# # # | Id | Name    | Email            | cdate               |
# # # +----+---------+------------------+---------------------+
# # # |  5 | Ross    | Ross@csdn.com    | 2019-02-17 16:30:10 |
# # # |  4 | Reachel | Reachel@csdn.com | 2019-02-16 16:30:10 |
# # # |  3 | Joey    | Joey@csdn.com    | 2019-02-15 16:30:10 |
# # # |  2 | Monica  | Monica@csdn.com  | 2019-02-14 16:30:10 |
# # # |  1 | Johnny  | Johnny@csdn.com  | 2019-02-13 16:30:10 |
# # # +----+---------+------------------+---------------------+
# # # 5 rows in set (0.00 sec)

# # #2. 从两个表中查询点赞数最高的5条博客信息，要求显示字段：（博文id，标题，点赞数，会员名） =============================================================================================================================
# # # mysql> select blog.id, blog.title, blog.pcount, users.name from blog, users 
# # #      > where blog.uid = users.id order by blog.pcount desc limit 5;
# # # +----+-------------------------------------------------------------------------------+--------+---------+
# # # | id | title                                                                         | pcount | name    |
# # # +----+-------------------------------------------------------------------------------+--------+---------+
# # # |  3 | 马跃成：像买汽车一样人人都能买得起房的时代快到了？               	               |  26871 | Joey    |
# # # | 15 | 我面试过没有上万人也有十几个，简历要这么写才有hr要你                 	           |  21277 | Reachel |
# # # |  5 | 这个妈妈真粗心，飞机起飞后才发现宝宝还落在候机楼(图)                     	       |  12687 | Reachel |
# # # | 11 | 案例上手 Python 数据可视化                                                	   |   9978 | Joey    |
# # # |  9 | 在大型项目开发上，Python 真是个“烂”语言！                                        |   9456 | Johnny  |
# # # +----+-------------------------------------------------------------------------------+--------+---------+

# # #3. 统计每个会员的发表博文数量（降序），要求显示字段（会员id号，姓名，博文数量） =============================================================================================================================
# # # mysql> select users.id, users.name, count(blog.title) from users, blog 
# # #      > where blog.uid = users.id group by users.id order by count(blog.title) desc;
# # # +----+---------+-------------------+
# # # | id | name    | count(blog.title) |
# # # +----+---------+-------------------+
# # # |  4 | Reachel |                 8 |
# # # |  1 | Johnny  |                 4 |
# # # |  5 | Ross    |                 2 |
# # # |  3 | Joey    |                 2 |
# # # +----+---------+-------------------+
# # # 4 rows in set (0.00 sec)

# # #4. 获取会员的博文平均点赞数量最高的三位。显示字段（会员id，姓名，平均点赞数） =============================================================================================================================
# # # mysql> select users.id, users.name, round(avg(blog.pcount)) from users, blog 
# # #      > where blog.uid = users.id group by users.name order by avg(blog.pcount) desc limit 3;
# # # +----+---------+-------------------------+
# # # | id | name    | round(avg(blog.pcount)) |
# # # +----+---------+-------------------------+
# # # |  3 | Joey    |                   18425 |
# # # |  4 | Reachel |                    8195 |
# # # |  1 | Johnny  |                    7413 |
# # # +----+---------+-------------------------+
# # # 3 rows in set (0.00 sec)

# # #5. 删除没有发表博文的所有会员信息。=============================================================================================================================
# # # mysql> select users.id, users.name, count(blog.title) from users left join blog on users.id = blog.uid group by users.name order by count(blog.title);
# # # +----+---------+-------------------+
# # # | id | name    | count(blog.title) |
# # # +----+---------+-------------------+
# # # |  2 | Monica  |                 0 | * target
# # # |  5 | Ross    |                 2 |
# # # |  3 | Joey    |                 2 |
# # # |  1 | Johnny  |                 4 |
# # # |  4 | Reachel |                 8 |
# # # +----+---------+-------------------+
# # # 5 rows in set (0.00 sec)
# # # mysql> delete from users using users left join blog on users.id = blog.uid where blog.title is null;
# # # Query OK, 1 row affected (0.01 sec)
# # # mysql> select * from users;
# # # +----+---------+------------------+---------------------+
# # # | Id | Name    | Email            | cdate               |
# # # +----+---------+------------------+---------------------+
# # # |  1 | Johnny  | Johnny@csdn.com  | 2019-02-13 16:30:10 |
# # # |  3 | Joey    | Joey@csdn.com    | 2019-02-15 16:30:10 |  * id 2 Monica, who doesn't have any posts, has been deleted!
# # # |  4 | Reachel | Reachel@csdn.com | 2019-02-16 16:30:10 |
# # # |  5 | Ross    | Ross@csdn.com    | 2019-02-17 16:30:10 |
# # # +----+---------+------------------+---------------------+
# # # 4 rows in set (0.00 sec)

#3) 請參考ATM.py






from module.model_sql import *   #from model_sql import Sql_connect class


#Main part
#to connect server
gosql = Sql_connect()  #server parameters are all refered from module.config.py 


#============================to CREATE table, write sql statement manually========================================================
# gosql.sql = '''
# CREATE TABLE blog (
  # `Id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID號',
  # `title` varchar(100) NOT NULL COMMENT '標題',
  # `abstract` varchar(200) NOT NULL COMMENT '摘要',
  # `content` text NOT NULL COMMENT '博文內容',
  # `uid` int unsigned NULL COMMENT '用戶標誌',
  # `pcount` int NOT NULL default '0' COMMENT '點讚數',
  # `flag` tinyint(1) default '0' COMMENT '狀態(0:新建,1:發布,2:刪除)',
  # `cdate` datetime NOT NULL COMMENT '註冊時間',
  # PRIMARY KEY (`Id`)
# ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
# '''
# gosql.create()


#============================to DROP table, write sql statement manually========================================================
# gosql.sql = "drop table stu"
# gosql.drop()


#============================to ALTER table, write sql statement mostly manually================================================
# table_name = "users"
# action = ""  
# the_rest = "CHARSET=utf8mb4"
# gosql.alter(table_name,action,the_rest)


#============================to INSERT into table, only can insert one row at a time-> object.insert(table_name,fields,values)=====
# table_name = "users"
# tup1 = "id","name","email","cdate" 					 
# tup2 = ''' 
# ("2","Monica","Monica@csdn.com","2019-2-14 16:30:10")
# '''  													
# gosql.insert(table_name,tup1,tup2)     #tup1 for fields, tup2 for values  


#============================to DELETE threads.====================================================================================
# table_name = "stu"
# where = "id = '2'"
# gosql.delete(table_name,where)


#============================to UPDATE set of table with condition ->object.update(table_name,set_field,set_value,condition)=======
# table_name = "users"
# set_field = "cdate"
# set_value = "2019-2-17 16:30:10"
# condition = "id = '5'"  #make sure the value under the condition is quoted! 
# gosql.update(table_name,set_field,set_value,condition)


#============================to SELECT threads, write sql statement manually========================================================
# gosql.sql = '''select users.id, users.name, count(blog.title) from users left join blog on users.id = blog.uid group by users.name order by count(blog.title)'''
# gosql.select()

#============================to fully & manually execute sql statements, write at here and call exe(True) directly.================
# gosql.sql = ''''''
# gosql.exe(True)    *passing True so data will be all fetehed and printed.



